<?php

namespace App\Http\Controllers\Admin\ExportReport;

use Dompdf\Exception;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use DB;
use Illuminate\Support\Facades\Storage;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\IOFactory;
// use PhpParser\Node\Stmt\DeclareDeclare;
use App\Http\Controllers\Admin\ExportReport\ExportReportPublicController;

class ExportReportShanDongZaoZhuangController extends Controller
{
    private $setting;
    private $spreadsheet;
    private $worksheet;
    private $ExportPublic;
    private $data = [];
    /* 导出Excel 检测报告:要求每种相同类型/额定电压的工器具出一个报告*/
    public function export(Request $request) {
        // dd(111);
        $this->ExportPublic = new ExportReportPublicController();
        $contract_id = $request->id; //批次 id
        $data = [];
        //查询当前批次的信息
        $data['batch'] = $data_batch = DB::table('a_contract')->find($contract_id);
        $data['status_time_info'] = DB::table('a_status_time')->where('contract_id', $contract_id)->first();
        $batchNum = $data_batch->batchNum;
        $storeFile = 'reports/'.$batchNum.'.zip';
        $disk = Storage::disk('public');
        $new = data_get($request,'new',0);
        if($disk->exists($storeFile) || $disk->exists("reports/{$batchNum}.xls")){
            if($data_batch->status > 13 && $new == 0) {
                //return $disk->download($storeFile);
            }else{
                $disk->delete($storeFile);
            }
        }
        $this->setting = DB::table('setting')->where('id', '1')->select('report_num_pre','name_rep')->first(); 
        //============== 样品概要分组 =======================================
        $zu_samples = [];
        $samples = DB::table('a_sample_info as a')
            ->leftJoin('a_contract_gaiyao as b','a.contract_gaiyao_id','=','b.id')
            ->where('b.contract_id',$contract_id)
            ->select('a.*','b.tool_id','b.tool_name','b.vol_id','b.voltage','b.class_id','b.class','b.std_id','b.std_num','b.item_ids')
            ->get();
        foreach ($samples as $sample) {
            $zu_samples["{$sample->tool_name}-{$sample->voltage}-{$sample->tool_id}-{$sample->vol_id}"][$sample->std_id][] = $sample;
        }
        $data['zu_samples'] = $zu_samples;
        $data['dir'] = $zip_dir = "./storage/reports/{$data['batch']->batchNum}";
        $this->data = $data;
        return $this->setZuSamples();
    }

    private function setZuSamples()
    {
        $data = $this->data;
        $zu_samples = $data['zu_samples'];
        $batchNum = $data['batch']->batchNum;
        foreach ($zu_samples as $tool_vol => $stdSamples) {
            $data['tool_vol'] = $tool_vol;
            $data['std_nums'] = $data['tester_names'] = [];
            $this->data = $data;
            $this->setPagesSamples(); //设置每一页的样品
            $this->setFM(); //添加封面
            $this->saveSheet();
        }
        return $this->download($data);
    }
    //设置所有标准下的样品
    private function setPagesSamples()
    {
        $data = $this->data;
        $stdSamples = $data['zu_samples'][$data['tool_vol']];
        foreach($stdSamples as $std => $samples){
            $data['std_id'] = $std;
            if ($samples) {
                $data['std_nums'][] = $samples[0]->std_num;
                $testerNames = array_column($samples,'tester_names');
                foreach($testerNames as $names){
                    $names = explode('，',$names);
                    foreach($names as $name){
                        $data['tester_names'][] = $name;
                    }
                }
            }
            $this->data = $data;
            $this->setPageSamples($data);
        }
        
    }
    //设置每个标准下的样品
    private function setPageSamples()
    {
        $data = $this->data;
        $samples = $data['zu_samples'][$data['tool_vol']][$data['std_id']];
        $batchNum = $data['batch']->batchNum;
        // dd($samples,$data);
        $info = $samples[0];
        $data['info_pos'] = $info_pos = DB::table('a_excel_pos as B')
            ->leftJoin('a_std_tool_rel as A', 'A.id', '=', 'B.std_tool_rel_id')
            ->where(['A.std_id' => $info->std_id, 'A.tool_id' => $info->tool_id, 'B.is_default' => 1])
            ->select('B.*', 'A.cycle', 'A.ctime', 'A.remark')
            ->first();
        if (!$info_pos) {
            return "{$info->tool_name} 未指定数据在模板文件中的位置";
        }
        $posname = $info_pos->name === '默认' ? '' : $info_pos->name;
        # 检测模版文件是否存在
        $temp_tool = "./storage/ExcelTemplate/{$info->std_id}/{$info->tool_name}{$posname}.xls"; //考虑到兼容Linux，斜杠用“/”
        if (!file_exists($temp_tool)) {
            return "{$info->tool_name}的模板文件不存在";
        }
        /***********************************************样品  start***************************************************/
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($temp_tool);
        $worksheet = $spreadsheet->getSheet(0);
        //============ 设置眉头眉尾内容和页边距 start ==============
            $setting = $this->setting;
            $report_num_pre = $setting->report_num_pre; //报告编号前缀
            $company_name = $setting->name_rep;  //获取检测公司名称
            $headerContent = "&R{$company_name}     {$report_num_pre}{$batchNum}"; //眉头内容
            $footerContent = '&R&12第 &P 页，共 &N 页'; //页脚内容
            $headerPageMargins = 0.4; //眉头的页边距
            $footerPageMargins = 0.1; //页脚的页边距
            $bottomPageMargins = 0.3; //下边的页边距
            $worksheet->getHeaderFooter()
                    ->setOddHeader($headerContent)
                    ->setEvenHeader($headerContent)
                    ->setOddFooter($footerContent)
                    ->setEvenFooter($footerContent);
            $worksheet->getPageMargins()
                    ->setHeader($headerPageMargins)
                    ->setFooter($footerPageMargins)
                    ->setBottom($bottomPageMargins);
        //============ 设置眉头眉尾内容和页边距 end ==============
        $worksheet->setTitle($info->tool_name.$info->std_id);
        $this->worksheet = $worksheet;
        $this->data = $data;
        $this->setSamples();
        if (!$this->spreadsheet) {
            $this->spreadsheet = $spreadsheet;
        }else{
            $this->spreadsheet->addExternalSheet($worksheet); //添加外部工作表
        }
    }
    //设置样品信息
    private function setSamples(){
        $this->setBaseInfo();
        $data = $this->data;
        $info_pos = $data['info_pos'];
        $worksheet = $this->worksheet;
        $samples = $data['zu_samples'][$data['tool_vol']][$data['std_id']];
        //------------------------------------------ 样品数据 start---------
            $rowIndex = $initRowIndex = $info_pos->line_num;
            $sampleMergeCells = $this->ExportPublic->sampleMergeCells($worksheet,$info_pos); //样品内容合并样式
            // dd($sampleMergeCells);
            $this->data['itemResDef'] = [];
            foreach($samples as $kk => $sample){
                $this->data['sample_key'] = $kk;
                if($kk > 0) {
                    # 键>0 代表不是第一次循环
                    $worksheet->insertNewRowBefore($rowIndex, $info_pos->sample_rowspann); //在第x行之前插入1个新行
                    $this->ExportPublic->toMergeCells($kk,$info_pos,$sampleMergeCells,$worksheet);
                    $this->ExportPublic->sampleDefVal($worksheet,$info_pos,$kk,$sampleMergeCells);
                }
                $this->setSampleInfo($rowIndex);
                $rowIndex += 1;
            }

        //------------------------------------------ 样品数据 end---------
        //------------------------------------------ 检测结论，检验结论 start---------
            $ress = array_column($samples, 'res');
            $total = count($samples);
            $hegeCount = $unhegeCount = $weiZhiCount = 0;
            foreach ($ress as $res) {
                switch ($res) {
                    case 1:
                        $hegeCount += 1;
                    break;
                    case 2:
                        $unhegeCount += 1;
                    break;
                    default:
                        $weiZhiCount += 1;
                        break;
                }
            }
            $jlcellstr = trim($worksheet->getCell('A' . $rowIndex)->getValue());
            if ($jlcellstr === '检测结论' || $jlcellstr === '检验结论') {
                $range = $worksheet->getCell('A' . $rowIndex)->getMergeRange();
                $rangeAry = explode(':', $range);
                $ABCpos = dec2ABC(ABC2decimal(substr($rangeAry[1], 0, 1)) + 1);
                $str = "总数：{$total} 件,其中合格：{$hegeCount} 件，不合格：{$unhegeCount} 件";
                $str .= $weiZhiCount ? "未知结论：{$weiZhiCount} 件": "";
                $worksheet->getCell($ABCpos . $rowIndex)->setValue($str);
            }
        //------------------------------------------ 检测结论，检验结论 end---------
    }
    private function setBaseInfo(){
        $data = $this->data;
        $worksheet = $this->worksheet;
        $info_pos = $data['info_pos'];
        $samples = $data['zu_samples'][$data['tool_vol']][$data['std_id']];
        $testTimes = array_column($samples, 'test_time');
        // dd($testTimes, $samples,$data);
        $sampleCount = count($samples);
        //未指定行号
        if ($info_pos->line_num === '' || intval($info_pos->line_num) < 5) {
            //为了防止内存泄漏，建议用完手动清理
            $spreadsheet->disconnectWorksheets();
            unset($spreadsheet);
            return "{$v->tool_name} 未指定数据在模板文件中的行号";
        }

        $DY_RiQi = strtotime($data['batch']->created_at); //到样日期
        if ($info_pos->come_date !== '') {
            $comeDateCell = $info_pos->come_date;
            $worksheet->getCell($comeDateCell)->setValue(date("Y年m月d日", $DY_RiQi)); //到样日期
        }
        //------------------------------------------检测日期---------
            if ($info_pos->test_date !== ''){
                $testDateCell = $info_pos->test_date;
                $minTestTime = min($testTimes);
                $maxTestTime = max($testTimes);
                $testTime = $minTestTime && $maxTestTime && $minTestTime != $maxTestTime ? ($minTestTime.'~'.$maxTestTime) : ($maxTestTime ?: ($minTestTime?:''));
                $worksheet->getCell($testDateCell)->setValue($testTime); //检测日期
            }
        //-------------------------------------------------------------------
        if ($info_pos->sample_count !== '') {
            $worksheet->getCell($info_pos->sample_count)->setValue($sampleCount); //样品数量
        }
        $contract_id = $data['batch']->id;
        $weather_data = DB::table('a_item_data as B')
                ->select('temp', 'hum', 'pressure', 'wea')
                ->leftJoin('a_contract_gaiyao as A', 'A.id', '=', 'B.contract_gaiyao_id')
                ->where('B.contract_id', $contract_id)
                ->whereIn('B.sample_info_id', array_column($samples, 'id'))
                ->where('item_id', '!=', 61)
                ->get()->toArray();
        // dd($temp_hum_pressure_data);
        
        $se = $this->ExportPublic::tempHumPressureSE($weather_data);
        [$temp, $hum, $pressure] = $se;
        // dd($se);
        //------------------------------------------ 温度 start---------
            if ($info_pos->temp !== '') {
                $worksheet->getCell($info_pos->temp)->setValue($temp); //温度
            }
        //------------------------------------------ 温度 end ---------
        //------------------------------------------ 相对湿度 start---------
            if ($info_pos->hum !== '') {
                $worksheet->getCell($info_pos->hum)->setValue($hum); //相对湿度
            }
        //------------------------------------------ 相对湿度 end ---------
        //------------------------------------------ 大气压力 start---------
            if ($info_pos->pressure !== '') {
                $worksheet->getCell($info_pos->pressure)->setValue($pressure); //大气压力
            }
        //------------------------------------------ 大气压力 end ---------
        //------------------------------------------ 天气 start---------
            $wea = current(ary_filter(array_column($weather_data,'wea')));
            if ($info_pos->wea !== '' && $wea) {
                $worksheet->getCell($info_pos->wea)->setValue($wea);
            }
        //------------------------------------------ 天气 end ---------
    }

    //试验数据
    private function setSampleInfo($rowIndex){
        $data = $this->data;
        $info = $data['zu_samples'][$data['tool_vol']][$data['std_id']][$data['sample_key']];
        $info_pos = $data['info_pos'];
        $worksheet = $this->worksheet;
        //========== 序号 start========
            if ($info_pos->serial_num1 !== '') {
                $worksheet->getCell($info_pos->serial_num1 . $rowIndex)->setValue(1); //
            }
        //========== 序号 end ========
        //========== 样品号 start ========
            if ($info_pos->sample_num1 !== '') {
                $worksheet->getCell($info_pos->sample_num1 . $rowIndex)->setValue(substr($info->bar_code, 6) . '#'); //样品号
            }
        //========== 样品号 end ========
        //========== 自编号 start ========
            if ($info_pos->self_num1 !== '') {
                $worksheet->getCell($info_pos->self_num1 . $rowIndex)->setValue($info->self_num); //自编号
            }
        //========== 自编号 end ========
        //========== 额定电压 start ========
            if ($info_pos->voltage1 !== '') {
                $voltageWithoutKV = str_replace("kV", "", $info->voltage); //将额定电压中的 kV去掉
                $worksheet->getCell($info_pos->voltage1 . $rowIndex)->setValue($voltageWithoutKV); //额定电压
            }
        //========== 额定电压 end ========
        //========== 分类 start ========
            if ($info_pos->class1 !== '') {
                $worksheet->getCell($info_pos->class1 . $rowIndex)->setValue($info->class); //分类
            }
        //========== 分类 end ========
        //========== 判定 start ========
            $panding = '';
            if ($info->res == 1) {
                $panding = '合格';
            }
            elseif ($info->res == 2) {
                $panding = '不合格';
            }
            if ($info_pos->res1 !== '') {
                $worksheet->getCell($info_pos->res1 . $rowIndex)->setValue($panding); //判定
            }
        //========== 判定 end ========
        //========== 有效日期 start ========
            if ($info_pos->valid_date1 !== '' && $info_pos->cycle) {
                if ($info->res === 1) {
                    //合格
                    $test_time = $info->test_time; //试验日期
                    $cycle = ($info_pos->cycle) * 12; //周期

                    $valid_date = date("Y-m-d", strtotime("+$cycle month", strtotime($test_time)));
                    $first_date = date("Y-m-d", strtotime("first day of +$cycle month", strtotime($test_time)));
                    if (substr($valid_date, 0, 7) !== substr($first_date, 0, 7)) {
                        $valid_date = date("Y-m-d", strtotime("last day of +$cycle month", strtotime($test_time)));
                    }

                    $valid_date = date('Y-m-d', strtotime("-1 day", strtotime($valid_date)));
                }
                else {
                    # 不合格
                    $valid_date = "—";
                }
                $worksheet->getCell($info_pos->valid_date1 . $rowIndex)->setValue($valid_date); //有效日期
            }
        //========== 有效日期 end ========
        $vv = $info;
        //------------------------------------------ 试验条件 start ---------
            $key_condition = DB::table('a_condition_key')->select('id', 'item_id', 'pos_start', 'pos_end', 'std_tool_item_rel_id')->where([
                ['std_id', '=', $info->std_id],
                ['tool_id', '=', $info->tool_id],
                ['pos_start', '!=', ''],
                ['pos_end', '!=', '']
            ])->get();
            foreach($key_condition as $vvv) {
                $arr_item_id = explode(',', $info->item_ids); //字符串 --》数组
                $b = in_array($vvv->item_id, $arr_item_id); //in_array($value, $array);
                if($b) {
                    $val_condition = DB::table('a_condition')->where('condition_key_id', '=', $vvv->id)->whereIn('vol_class_id', [0, $vv->vol_id, $vv->class_id])->value('val');
                }
                else {
                    $val_condition = '/';
                }
                $vvvstart = $vvv->pos_start;
                $vvvend = $vvv->pos_end;

                if($vvvstart && $vvvend) {
                    $worksheet->mergeCells($vvvstart.$rowIndex.':'.$vvvend.$rowIndex); //试验条件  合并单元格
                    $worksheet->getCell($vvvstart.$rowIndex)->setValue($val_condition); //试验条件
                }
            } //内内层 foreach 结尾
        //------------------------------------------ 试验条件 end ---------
        //------------------------------------------ 试验数据、检测结果 start ---------
            $std_tool_item_rel = DB::table('a_std_tool_item_rel')->select('id', 'testitem_id', 'pos_start', 'pos_end')->where([
                ['std_id', '=', $info->std_id],
                ['tool_id', '=', $info->tool_id],
            ])->get();
            // dump($std_tool_item_rel);
            foreach($std_tool_item_rel as $relK=> $vvv) {
                //------------------------------------------ 试验数据 start ---------
                    $key_data = DB::table('a_testdata_key')->select('id', 'item_id', 'pos_start', 'pos_end', 'std_tool_item_rel_id')->where([
                        ['std_tool_item_rel_id', '=', $vvv->id],
                        ['pos_start', '!=', ''],
                        ['pos_end', '!=', '']
                    ])->get();
                    foreach($key_data as $vvv2) {
                        $arr_item_id = explode(',', $vv->item_ids); //字符串 --》数组
                        $b = in_array($vvv2->item_id, $arr_item_id); //in_array($value, $array);
                        if($b) {
                            $val_testdata = DB::table('a_input_data')->where([
                                ['sample_info_id', '=', $vv->id],
                                ['testdata_key_id', '=', $vvv2->id]
                            ])->value('val');
                            // dd( "{$val_testdata} --- {$vv->id}---- {$vvv2->id}");
                        }
                        else {
                            $val_testdata = '/';
                        }
                        try {
                            $vvv2start = $vvv2->pos_start;
                            $vvv2end = $vvv2->pos_end;
                        } catch(\Exception $exception) {
                            $vvv2start = $vvv2end = '';
                        }
                        if($vvv2start && $vvv2end) {
                            $worksheet->mergeCells($vvv2start.$rowIndex.':'.$vvv2end.$rowIndex); //试验数据  合并单元格
                            $worksheet->getCell($vvv2start.$rowIndex)->setValue($val_testdata." "); //试验数据
                        }
                    }
                //------------------------------------------ 试验数据 end ---------
                //------------------------------------------ 检测结果 start ---------
                    $vvvstart = $vvv->pos_start;
                    $vvvend = $vvv->pos_end;
                    if($vvvstart && $vvvend) {
                        $arr_item_id = explode(',', $info->item_ids); //字符串 --》数组
                        $b = in_array($vvv->testitem_id, $arr_item_id); //in_array($value, $array);
                        if($b) {
                            $test_item = DB::table('a_item_data')->where([
                                ['sample_info_id', '=', $info->id],
                                ['item_id', '=', $vvv->testitem_id]
                            ])->select('remark','res')->first();
                            $test_res = $test_item->res;
                            if ($data['sample_key'] == 0) {
                                $this->data['itemResDef'][$relK] = $worksheet->getCell($vvvstart.$info_pos->line_num)->getValue();
                            }

                            //结论,0-不符合，1-符合，2-未录入
                            // dump($info->bar_code.' '.$vvv->testitem_id.'=>'.$vvvstart.$info_pos->line_num.$data['itemResDef'][$vvvstart]);
                            if($test_res == 0) {
                                $remark = $test_item->remark;
                                //$test_res = $remark && trim($remark) != '无'? $remark: '不符合';
                                $test_res = '不符合';
                            }
                            elseif($test_res == 1) {
                                //$test_res = $this->data['itemResDef'][$relK] ?: '符合';
                                $test_res = '符合';
                            }
                            elseif($test_res == 3) {
                                $test_res = '/';
                            }
                            else {
                                $test_res = '';
                            }
                        }
                        else {
                            $test_res = '/';
                        }
                        $worksheet->mergeCells($vvvstart.$rowIndex.':'.$vvvend.$rowIndex); //单项目的检测结果  合并单元格
                        $worksheet->getCell($vvvstart.$rowIndex)->setValue($test_res); //单项目的检测结果
                    }
                //------------------------------------------ 检测结果 end ---------
            }
        //------------------------------------------ 试验数据、检测结果 end   ---------
    }

    //封面设置
    private function setFM(){
        $data = $this->data;
        $template_file = './storage/ExcelTemplate/单样品封面.xls'; //考虑到兼容Linux，斜杠用“/”
        if(!file_exists($template_file)) {
            return '封面模板文件不存在';
        }
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($template_file);
        $data_batch = $data['batch'];
        $batchNum = $data_batch->batchNum;
        $setting = $this->setting;
        $report_num_pre = $setting->report_num_pre; //报告编号前缀
        $company_name = $setting->name_rep;  //获取检测公司名称
        $company_names = explode('/',$company_name);
        // dd($company_names);
        $headerContent = "&R{$company_name}     {$report_num_pre}{$batchNum}"; //眉头内容
        $headerPageMargins = 0.4; //眉头的页边距
        $footerContent = '&R&12第 &P 页，共 &N 页'; //页脚内容
        $footerPageMargins = 0.1; //页脚的页边距
        $bottomPageMargins = 0.3; //下边的页边距
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($template_file); //读取模板文件
        $worksheet = $spreadsheet->getSheetByName('封面'); //按索引获取表单，从 0 开始
        $worksheet->setTitle('封面'); //重命名工作表
        $worksheet->getHeaderFooter()->setOddFooter($footerContent); //页脚  Odd
        $worksheet->getHeaderFooter()->setEvenFooter($footerContent); //页脚  Even
        $worksheet->getPageMargins()->setFooter($footerPageMargins); //设置页脚的页边距
        $worksheet->getPageMargins()->setBottom($bottomPageMargins); //设置下边的页边距
        $worksheet->setCellValue('Y22',$report_num_pre . $batchNum); //报告编号前缀 + 报告编号
        $worksheet->setCellValue('P25',$data_batch->entrusted_company);
        list($tool_name,$voltage,$tool_id,$vol_id) = explode('-',$data['tool_vol']);
        $worksheet->setCellValue('P26',$tool_name);
        $worksheet->setCellValue('P27',$voltage);
        $worksheet->setCellValue('P28','预防性试验');
        // dd($company_name);
        $startRow = 44;
        foreach ($company_names as $item_name) {
            $cell = 'A'.$startRow;
            $worksheet->setCellValue($cell,$item_name);
            $worksheet->mergeCells("{$cell}:AZ{$startRow}");
            // $worksheet->getStyle($cell)->applyFromArray($styleArray);
            $startRow += 1;
        }
        $this->spreadsheet->addExternalSheet($worksheet,0); //添加外部工作表
        $worksheet = $spreadsheet->getSheetByName('封面2');
        
        $manufacturer =  isset($data['zu_samples'][$data['tool_vol']][2][0]->manufacturer)?$data['zu_samples'][$data['tool_vol']][2][0]->manufacturer:'/';
        $worksheet->setCellValue('E1',$data_batch->entrusted_company);
        $worksheet->setCellValue('E4',$tool_name);
        $worksheet->setCellValue('N1',$manufacturer);
        $worksheet->setCellValue('N4',$voltage);
        $worksheet->setCellValue('N7','见正文');
        //2023 3.1修改，封面2显示检测日期
        $testTime = '';
        $info_pos = $data['info_pos'];
        $samples = $data['zu_samples'][$data['tool_vol']][$data['std_id']];
        $testTimes = array_column($samples, 'test_time');
        if ($info_pos->test_date !== ''){
            $testDateCell = $info_pos->test_date;
            $minTestTime = min($testTimes);
            $maxTestTime = max($testTimes);
            $testTime = $minTestTime && $maxTestTime && $minTestTime != $maxTestTime ? ($minTestTime.'~'.$maxTestTime) : ($maxTestTime ?: ($minTestTime?:''));
        }
        $worksheet->setCellValue('N10',$testTime);
        $worksheet->setCellValue('E7','委托送检');
        $worksheet->setCellValue('E10','预防性试验');
        $worksheet->setCellValue('E13',implode('，',array_unique($data['std_nums'])));
        $testerNames = implode('，',array_unique(array_filter($data['tester_names'])));
        $worksheet->setCellValue('E32',$testerNames);
        $worksheet->setCellValue('E35',$data_batch->remark);
        $this->spreadsheet->addExternalSheet($worksheet,1); //添加外部工作表
        /***********************************************封面2  end***************************************************/
    }

    //保存
    private function saveSheet(){
        // dd($data);
        $data = $this->data;
        $spreadsheet = $this->spreadsheet;
        $batchNum = $data['batch']->batchNum;
        $tool_vol = $data['tool_vol'];
        list($tool_name,$voltage,$tool_id,$vol_id) = explode('-',$tool_vol);
        $file_name = "检测报告{$batchNum}_{$tool_name}_".($voltage == '/' || $voltage == '无' ? '' : $voltage);
        $file_name = $file_name.'.xls';
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
        $writer->setPreCalculateFormulas(false); //禁用公式预先计算
        $dir = $data['dir'];
        if (!is_dir($dir)) {
            mkdir($dir);
        }
        $writer->save("{$data['dir']}/{$file_name}"); //直接在浏览器下载
        //为了防止内存泄露，建议用完手动清理
        $spreadsheet->disconnectWorksheets();
        $this->spreadsheet = null;
    }

    //下载
    private function download($data){
        $data_batch = $data['batch'];
        $batchNum = $data_batch->batchNum;
        $disk = Storage::disk('public');
        $dir = 'reports/'.$batchNum;
        $fileList = $disk->files($dir);
        $storeFile = "./storage/reports/{$batchNum}.zip";
        // dd($zipname);
        $zip = new \ZipArchive();
        $zip->open($storeFile,\ZipArchive::CREATE);   //打开压缩包
        foreach($fileList as $file){
            $file = './storage/'.$file;
            $filename = basename($file);
            //向压缩包中添加文件
            $zip->addFile($file,basename($file));  
        }
        $zip->close();  //关闭压缩包
        $disk = Storage::disk('public');
        $disk->deleteDirectory("reports/{$batchNum}");
        $file = "./storage/reports/{$batchNum}.zip";
        if($data_batch->status > 13) {
            return response()->download($file);
        }else{
            return response()->download($file)->deleteFileAfterSend(true);
        }
    }
}
